package Utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;





public class DBUtil {
	private static final String SQL = "SELECT * FROM ";
	private final static Logger LOGGER = LoggerFactory.getLogger(DBUtil.class);

	private static Connection conn;
	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * get table PK
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static List<String> getTablePK(String tableName,Connection conn) {

		List<String> list = new ArrayList<String>();
		ResultSet rs;
		try {
			rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, tableName);
			if (null == rs) {
				return list;
			}
			while (rs.next()) {
			//	pkStr =pkStr+","+ "\""+rs.getString("COLUMN_NAME")+"\"";
				list.add(rs.getString("COLUMN_NAME"));

//				System.out.println( rs.getString("DATA_TYPE"));
			}
//			if (pkStr.startsWith(",")) {
//				pkStr = pkStr.substring(1);
//			}
			// final
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
 
 
		return list;
	}

	public static String getOnlyOneTablePK(String tableName,Connection conn) {
		String pkStr = "";
		ResultSet rs;
		try {
			rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, tableName);
			if (null == rs) {
				return pkStr;
			}
			while (rs.next()) {
				pkStr= rs.getString("COLUMN_NAME");
				break;

//				System.out.println( rs.getString("DATA_TYPE"));
			}

			// final
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}


		return pkStr;
	}

	public static List getDataBySQL(String sql, Connection conn) {
		Statement stmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String[] arr = new String[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < arr.length; i++) {
					arr[i] = rs.getString(i + 1);
				}
				list.add(arr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public static synchronized Connection getConnection(String url, String username, String password)
			throws SQLException {

		if (conn == null || conn.isClosed()) {

			try {
				return DriverManager.getConnection(url, username, password);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}

		return conn;

	}

	

	

    /**
     * 关闭数据库连接
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("close connection failure", e);
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     */
    public static List<String> getTableNames(Connection conn) {
        List<String> tableNames = new ArrayList<>();
       /* conn.getCatalog(), "root", null, new String[]{"TABLE"}*/
        ResultSet rs = null;
      
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables( conn.getCatalog(), null, null, new String[] { "TABLE" });
            while(rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            LOGGER.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
               
            } catch (SQLException e) {
                LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    public static List<String> getColumnNames( Connection conn,String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
  
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                   // closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     * @param tableName
     * @return
     */
    public static List<String> getColumnTypes(Connection conn,String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接

        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                   // closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     * @param tableName
     * @return
     */
    public static List<String> getColumnComments(Connection conn,String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接

        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                   // closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }
    
    

	/**
	 * @return 获取项目所有的表和字段
	 */
	public static List<Map<String, Object>> getTableAndColumns(Connection conn,String dbname) {
		String sql1 = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + dbname + "'";
		List<Map<String, Object>> tables = queryForList(conn,sql1);
		for (Map<String, Object> table : tables) {
			// 查询表结构
			String sql = "select COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT, IS_NULLABLE from information_schema.columns where table_schema = '"
					+ dbname + "' and table_name = '" + table.get("TABLE_NAME") + "'";
			List<Map<String, Object>> columns =queryForList(conn,sql);
			for (Map<String, Object> map : columns) {
				map.put("IS_NULLABLE", "NO".equals(map.get("IS_NULLABLE")) ? "是" : "否");
				map.put("COLUMN_KEY", "PRI".equals(map.get("COLUMN_KEY")) ? "是" : "否");
			}
			table.put("columns", columns);
		}
		return tables;
	}



	/**
	 * queryForList
	 * @param sql
	 * @return
	 */
	public static List<Map<String, Object>> queryForList(Connection conn, String sql) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			
			ResultSet ret = conn.prepareStatement(sql).executeQuery();
			ResultSetMetaData rsmd = ret.getMetaData();
			int columnCount = ret.getMetaData().getColumnCount();
			while (ret.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i < columnCount + 1; ++i)
					map.put(rsmd.getColumnName(i), ret.getObject(i));

				list.add(map);
			}
			ret.close();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据库查询失败：" + e.getMessage());
		}
		return list;
	}
  
}
